#include "Stdafx.h"
#include "DBUtil.h"

DBUtil::DBUtil(void)
{
	this->conStr = "Driver={SQL Server};Server=(local)\\SQLEXPRESS;Database=Moonlight;UID=;PWD=";
}

DBUtil::~DBUtil(void)
{
	disconnect();
}

BOOL DBUtil::connect()
{
	CoInitialize(NULL);

	try
	{
		m_pConn.CreateInstance("ADODB.Connection");
	}
	catch(_com_error e)
	{
		AfxMessageBox(L"Create Instance Failed!\n" + e.Description());
		return false;
	}

	try
	{
		m_pConn->Open(conStr,"","",adModeUnknown);
	}
	catch(_com_error e)
	{
		AfxMessageBox(L"Connect Database Failed!\n" + e.Description());
		return false;
	}

	return true;
}

void DBUtil::disconnect()
{
	if( m_pConn->State == adStateOpen )
	{
		m_pConn->Close();
		m_pConn = NULL;
	}
}

Student* DBUtil::getStudentByID(CString studentNumber)
{
	_RecordsetPtr m_pRecordset = NULL;
	m_pRecordset = get_RecordsetByStudentID(studentNumber);
	if(m_pRecordset->GetRecordCount() != 0)
	{
		CString name;
		CString studentNumber;
		BOOL gender;
		CString college;
		CString major;
		CString roomNumber;
		CString rfidNumber;
		vector<Computer*> computers;

		name.Format((_bstr_t)m_pRecordset->GetCollect("name")); 
		studentNumber.Format((_bstr_t)m_pRecordset->GetCollect("stu_number")); 
		college.Format((_bstr_t)m_pRecordset->GetCollect("college"));
		major.Format((_bstr_t)m_pRecordset->GetCollect("major"));
		roomNumber.Format((_bstr_t)m_pRecordset->GetCollect("room_number"));
		rfidNumber.Format((_bstr_t)m_pRecordset->GetCollect("rfid_number"));

		Student* stu = new Student();
		stu->setName(name);
		stu->setStudentNumber(studentNumber);
		stu->setCollege(college);
		stu->setMajor(major);
		stu->setRfidNumber(rfidNumber);
		stu->setRoomNumber(roomNumber);

		computers = this->getComputerByStudentID(studentNumber);
		stu->setComputers(computers);

		m_pRecordset.Release();
		return stu;
	}
	else
	{
		m_pRecordset.Release();
		return NULL;
	}
}

_RecordsetPtr DBUtil::get_RecordsetByStudentID(CString studentNumber)
{
	CString sqlString("select * from Student where stu_number='");
	sqlString = sqlString + CString(studentNumber) + L"'";

	_RecordsetPtr m_pRecordset;
	try
	{
		m_pRecordset.CreateInstance(__uuidof(Recordset));
		m_pRecordset->CursorType = adOpenStatic;
		m_pRecordset->CursorLocation = adUseClient;
		m_pRecordset->CacheSize = 1000L;
		m_pRecordset->Open(_bstr_t(sqlString), m_pConn.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, adCmdText);
	}
	catch(_com_error e)
	{
		AfxMessageBox(L"Query Failed!\n" + e.Description());
		return NULL;
	}

	return m_pRecordset;
}

Computer* DBUtil::getComputerByID(CString rfidNumber)
{
	_RecordsetPtr m_pRecordset = NULL;
	m_pRecordset = get_RecordsetByStudentID(rfidNumber);
	if(m_pRecordset->GetRecordCount() != 0)
	{
		CString rfidNumber;
		CString studentNumber;
		CString vendor;
		CString type;
		CString color;
		CString size;
		CString weight;
		CString description;

		rfidNumber.Format((_bstr_t)m_pRecordset->GetCollect("rfid_number")); 
		studentNumber.Format((_bstr_t)m_pRecordset->GetCollect("stu_number"));
		vendor.Format((_bstr_t)m_pRecordset->GetCollect("vendor"));
		type.Format((_bstr_t)m_pRecordset->GetCollect("type"));
		color.Format((_bstr_t)m_pRecordset->GetCollect("color"));
		size.Format((_bstr_t)m_pRecordset->GetCollect("size"));
		weight.Format((_bstr_t)m_pRecordset->GetCollect("description"));

		Computer* com = new Computer();
		com->setRfidNumber(rfidNumber);
		com->setStudentNumber(studentNumber);
		com->setVendor(vendor);
		com->setType(type);
		com->setColor(color);
		com->setSize(size);
		com->setWeight(weight);
		com->setDescription(description);

		m_pRecordset.Release();
		return com;
	}
	else
	{
		m_pRecordset.Release();
		return NULL;
	}

}

_RecordsetPtr DBUtil::get_RecordsetByComputerID(CString rfidNumber)
{
	CString sqlString("select * from Computer where rfid_number='");
	sqlString = sqlString + CString(rfidNumber) + L"'";

	_RecordsetPtr m_pRecordset;
	try
	{
		m_pRecordset.CreateInstance(__uuidof(Recordset));
		m_pRecordset->CursorType = adOpenStatic;
		m_pRecordset->CursorLocation = adUseClient;
		m_pRecordset->CacheSize = 1000L;
		m_pRecordset->Open(_bstr_t(sqlString), m_pConn.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, adCmdText);
	}
	catch(_com_error e)
	{
		AfxMessageBox(L"Query Failed!\n" + e.Description());
		return NULL;
	}

	return m_pRecordset;
}

vector<Computer*> DBUtil::getComputerByStudentID(CString studentNumber)
{
	vector<Computer*> computers;

	CString sqlString("select * from Computer where stu_number='");
	sqlString = sqlString + CString(studentNumber) + L"'";

	_RecordsetPtr m_pRecordset;
	try
	{
		m_pRecordset.CreateInstance(__uuidof(Recordset));
		m_pRecordset->CursorType = adOpenStatic;
		m_pRecordset->CursorLocation = adUseClient;
		m_pRecordset->CacheSize = 1000L;
		m_pRecordset->Open(_bstr_t(sqlString), m_pConn.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, adCmdText);

		while( !m_pRecordset->adoEOF )
		{
			CString rfidNumber;
			CString studentNumber;
			CString vendor;
			CString type;
			CString color;
			CString size;
			CString weight;
			CString description;

			rfidNumber.Format((_bstr_t)m_pRecordset->GetCollect("rfid_number")); 
			studentNumber.Format((_bstr_t)m_pRecordset->GetCollect("stu_number"));
			vendor.Format((_bstr_t)m_pRecordset->GetCollect("vendor"));
			type.Format((_bstr_t)m_pRecordset->GetCollect("type"));
			color.Format((_bstr_t)m_pRecordset->GetCollect("color"));
			size.Format((_bstr_t)m_pRecordset->GetCollect("size"));
			weight.Format((_bstr_t)m_pRecordset->GetCollect("description"));

			Computer* com = new Computer();
			com->setRfidNumber(rfidNumber);
			com->setStudentNumber(studentNumber);
			com->setVendor(vendor);
			com->setType(type);
			com->setColor(color);
			com->setSize(size);
			com->setWeight(weight);
			com->setDescription(description);

			computers.push_back(com);

			m_pRecordset->MoveNext();
		}
	}
	catch(_com_error e)
	{
		AfxMessageBox(L"Query Failed!\n" + e.Description());
	}

	m_pRecordset.Release();
	return computers;
}